﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Connection
{
    public class UserDB : MarshalByRefObject
    {
        private string cmdtext;
        private ConnectDB connectDB = new ConnectDB();

        private MySqlConnection connection = null;
        #region Insert
        public int InsertUser(string Username, string Password)
        {
            string UserId = this.GenerateUserId();
            cmdtext = "INSERT INTO dbchat.user VALUES('" + UserId + "','" + Username + "','" + Password + "', " + 0 + ")";
            return connectDB.QueryData(cmdtext); 
        }
        #endregion

        #region Update
        public int UpdateIsOnline(string UserId, int IsOnline)
        {
            cmdtext = "UPDATE dbchat.user SET IsOnline = " + IsOnline + " WHERE UserId = '" + UserId + "'";
            return connectDB.QueryData(cmdtext);
        }
        #endregion

        #region Select
        public List<String> SelectUserId()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.getConnectionString());
            cmdtext = "SELECT UserId FROM dbchat.User";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<String> dsID = new List<String>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsID.Add(temp);
                }

                return dsID;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        //Select UserID by Username and Password 
        public String SelectUserId(string Username, string Password)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.getConnectionString());
            cmdtext = "SELECT UserId FROM dbchat.User WHERE Username = '" + Username + "' and Password = '" + Password +"'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                String sUserId;
                sUserId = ds.Tables[0].Rows[0][0].ToString();



                return sUserId;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        //Select UserID by Username 
        public String SelectUserId(string Username)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.getConnectionString());
            cmdtext = "SELECT UserId FROM dbchat.User WHERE Username = '" + Username + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                String sUserId;
                sUserId = ds.Tables[0].Rows[0][0].ToString();



                return sUserId;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public List<string> SelectIsUserOnline()
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.getConnectionString());
            cmdtext = "SELECT Username FROM dbchat.User WHERE IsOnline = " + 1 ;
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            List<String> dsUsername = new List<String>();

            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    String temp;
                    temp = ds.Tables[0].Rows[i][0].ToString();

                    dsUsername.Add(temp);
                }

                return dsUsername;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }

        public string SelectUsername(string UserId)
        {
            //connectDB.setConnectionString(ref connectionString);
            connection = new MySqlConnection(connectDB.getConnectionString());
            cmdtext = "SELECT Username FROM dbchat.User WHERE UserId = '" + UserId + "'";
            MySqlCommand cmd = new MySqlCommand(cmdtext, connection);
            DataSet ds = new DataSet();
            try
            {
                //mo ket noi
                connection.Open();
                MySqlDataAdapter adap = new MySqlDataAdapter();
                adap.SelectCommand = cmd;
                adap.Fill(ds);

                String sUsername;
                sUsername = ds.Tables[0].Rows[0][0].ToString();



                return sUsername;
            }
            catch
            {
                return null;
            }
            finally
            {
                //dong ket noi 
                if (connection != null)
                {
                    connection.Close();
                }
            }
        }
        #endregion

        #region Generate User Id
        public String GenerateUserId()
        {
            List<String> listUserId = this.SelectUserId();
            string generatedUserId;
            do
            {
                generatedUserId = "";

                var chars = "FGDF8G7S5D7FWHJRTGEWO8652958645869SG6F9D8G75XC7V46X436D3F649JGTGE46DF7HJ3H52GHMBVUHJXVBSDIY";
                var random = new Random();
                var result = new String(
                    Enumerable.Repeat(chars, 6)
                              .Select(s => s[random.Next(s.Length)])
                              .ToArray());
                generatedUserId += "USER" + result;
            } while (listUserId.Contains(generatedUserId));
            return generatedUserId;
        }
        #endregion

        public int Test() { return 0; }
    }
}